package com.wondream.myframework.app.common.excel;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.wondream.myframework.app.common.excel.annotation.ExcelColumn;
import com.wondream.myframework.app.common.excel.annotation.ExcelSheet;
import com.wondream.myframework.app.common.excel.annotation.Translate;
import com.wondream.myframework.app.common.excel.context.CfssPoiExporterContext;
import com.wondream.myframework.app.common.excel.domain.ExcelContext;
import com.wondream.myframework.app.common.excel.function.CfssExcelFunction;
import com.wondream.myframework.app.common.excel.function.FunctionRegister;
import com.wondream.myframework.app.common.excel.processor.DefaultRowProcessor;
import com.wondream.myframework.app.common.exception.BusinessExeceptionCode;
import com.wondream.myframework.app.common.log.Log;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.joda.time.DateTime;
import org.springframework.expression.Expression;
import org.springframework.expression.common.TemplateParserContext;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.springframework.expression.spel.support.StandardEvaluationContext;
import org.springframework.util.ReflectionUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.*;

/**
 * 通用excel导入功能
 */
@Slf4j
public class CfssPoiImporter {

    public static Map<Class, List> importExcel(String path, List<Class> entityCls) {
        final String op = "CfssPoiImporter.importExcel";
        List<ExcelContext> result;
        List<ExcelSheet> excelSheets = Lists.newArrayList();
        entityCls.forEach(cls -> {
            Annotation annotation = cls.getAnnotation(ExcelSheet.class);
            if (annotation.annotationType().isAssignableFrom(ExcelSheet.class)) {
                excelSheets.add((ExcelSheet) annotation);
            }
        });

        try {
            result = parseExcel(path, excelSheets);
            Map<Class, List> entitiesMap = transferExcelToEntity(result, excelSheets);
            return entitiesMap;
        } catch (Exception e) {
            log.warn(String.format("%s解析excel文件失败", op));
            throw BusinessExeceptionCode.SYS_ERROR.exp();
        }
    }

    public static Map<Class, List> importExcel(InputStream in, List<Class> entityCls) {
        final String op = "CfssPoiImporter.importExcel";
        List<ExcelSheet> excelSheets = Lists.newArrayList();
        List<ExcelContext> result;
        entityCls.forEach(cls -> {
            Annotation annotation = cls.getAnnotation(ExcelSheet.class);
            if (annotation.annotationType().isAssignableFrom(ExcelSheet.class)) {
                excelSheets.add((ExcelSheet) annotation);
            }
        });
        log.info("开始解析");
        try {
            result = parseExcel(in, excelSheets);
            Map<Class, List> entitiesMap = transferExcelToEntity(result, excelSheets);
            return entitiesMap;
        } catch (Exception e) {
            log.warn(String.format("%s解析excel文件失败", op));
            throw BusinessExeceptionCode.SYS_ERROR.exp();
        }

    }

    private static List<ExcelContext> parseExcel(InputStream in, List<ExcelSheet> excelSheets) throws Exception {
        final String op = "CfssPoiImporter.parseExcel";
        ArrayList<ExcelContext> ctxVec = Lists.newArrayList();
        XSSFWorkbook wb = null;
        try {
            long start = System.currentTimeMillis();
            log.info(Log.op(op).msg("Parse start").toString());
            wb = new XSSFWorkbook(in);
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                if (null != match(wb.getSheetAt(i).getSheetName(), excelSheets)) {
                    ctxVec.add(parseSheet(wb.getSheetAt(i), wb));
                }
            }
            long period = System.currentTimeMillis() - start;

            log.info(Log.op(op).msg("Parse Finish").kv("period", period).toString());
        } catch (Exception e) {
            throw e;
        } finally {
            IOUtils.closeQuietly(in);
        }
        return ctxVec;
    }

    private static List<ExcelContext> parseExcel(File file, List<ExcelSheet> excelSheets) throws Exception {
        final String op = "CfssPoiImporter.parseExcel";
        XSSFWorkbook wb = null;
        ArrayList<ExcelContext> ctxVec = Lists.newArrayList();
        try {
            // Start
            long start = System.currentTimeMillis();
            wb = new XSSFWorkbook(file);
            log.info(Log.op(op).msg("Parse start").kv("fileName", file.getName()).toString());
            int numberOfSheets = wb.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {

                if (match(wb.getSheetAt(i).getSheetName(), excelSheets) != null) {
                    ctxVec.add(parseSheet(wb.getSheetAt(i), wb));
                }
            }
            // Finish
            long end = System.currentTimeMillis();
            long period = end - start;

            log.info(Log.op(op).kv("period", period).msg("Parse Finish").toString());
        } catch (Exception e) {
            log.error(Log.op(op).msg("解析excel出错").kv("fileName", file.getName()).toString());
        }

        return ctxVec;
    }

    private static List<ExcelContext> parseExcel(String path, List<ExcelSheet> excelSheets) throws Exception {
        final String op = "CfssPoiImporter.parseExcel";
        XSSFWorkbook wb = null;
        InputStream is = null;
        ArrayList<ExcelContext> ctxVec = Lists.newArrayList();
        try {
            long startTime = System.currentTimeMillis();
            log.info(Log.op(op).msg("Parse start").kv("path", path).toString());
            is = new FileInputStream(path);
            wb = new XSSFWorkbook(is);
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                if (match(wb.getSheetAt(i).getSheetName(), excelSheets) != null) {
                    ctxVec.add(parseSheet(wb.getSheetAt(i), wb));
                }
            }
        } catch (Exception e) {
            throw e;
        } finally {
            IOUtils.closeQuietly(is);
        }
        return ctxVec;
    }


    private static Map<Class, List> transferExcelToEntity(List<ExcelContext> result, List<ExcelSheet> excelSheets) throws Exception {
        HashMap<Class, List> entitiesMaps = Maps.newHashMap();
        HashMap<String, Integer> headMap = Maps.newHashMap();
        int head;
        ExcelSheet anno = null;
        for (ExcelContext context : result) {
            List<List<String>> body = context.getBody();
            anno = match(context.getSheetName(), excelSheets);
            if (null == anno) {
                continue;
            }
            for (ExcelSheet sheet : excelSheets) {
                if (anno.classType().isAssignableFrom(sheet.classType())) {
                    if (!entitiesMaps.containsKey(sheet.classType())) {
                        entitiesMaps.put(sheet.classType(), Lists.newArrayList());
                    }
                    break;
                }
            }
            head = anno.headLine();
            for (int i = 0; i < body.size(); i++) {
                List<String> content = body.get(i);
                if (i == head - 1) {
                    headMap = parseHead(content, anno.classType());
                } else if (i >= head) {
                    List entries = entitiesMaps.get(anno.classType());
                    if (null == entries) {
                        continue;
                    }
                    entries.add(transferBodyToEntity(i, content, anno.classType(), headMap));
                }
            }
            anno = null;
        }
        return entitiesMaps;
    }

    private static ExcelContext parseSheet(XSSFSheet sheet, XSSFWorkbook wb) {
        final String op = "CfssPoiImporter.parseSheet";
        ExcelContext ctx = new ExcelContext();
        ctx.setExcelIndex(wb.getSheetIndex(sheet));
        ctx.setSheetName(sheet.getSheetName());
        int top = sheet.getFirstRowNum();
        int bottom = sheet.getLastRowNum();
        boolean nullFlag = true;
        for (int i = top; i <= bottom; i++) {
            try {
                nullFlag = true;
                XSSFRow row = sheet.getRow(i);
                ArrayList<String> bodyCtxVec = Lists.newArrayList();
                if (row == null) {
                    ctx.getBody().add(bodyCtxVec);
                    continue;
                }
                short begin = row.getFirstCellNum();
                short end = row.getLastCellNum();
                if (begin < 0) {
                    continue;
                }
                if (begin > 0) {
                    for (int j = 0; j < begin; j++) {
                        bodyCtxVec.add("");
                    }
                }
                for (int j = begin; j <= end; j++) {
                    XSSFCell cell = row.getCell(j);
                    String value = getMergedRegionValue(cell);
                    if (StringUtils.isNotBlank(value)) {
                        nullFlag = false;
                    }
                    bodyCtxVec.add(value);
                }
                if (!nullFlag) {
                    ctx.getBody().add(bodyCtxVec);
                }
            } catch (Exception e) {
                log.error(Log.op(op).msg("parse excel error").kv("rowIndex", i).toString());
            }
        }
        return ctx;
    }

    private static String getMergedRegionValue(XSSFCell cell) {
        if (null == cell) {
            return "";
        }
        XSSFSheet sheet = cell.getSheet();
        int row = cell.getRowIndex();
        int column = cell.getColumnIndex();
        int sheeMergedCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheeMergedCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    XSSFRow fRow = sheet.getRow(firstRow);
                    XSSFCell fCell = fRow.getCell(firstColumn);
                    return getCellValueText(fCell);
                }
            }
        }
        return getCellValueText(cell);
    }

    private static String getCellValueText(XSSFCell cell) {
        String cv = "";
        switch (cell.getCellType()) {
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cv = new DateTime(cell.getDateCellValue()).toString("yyyyMMdd");
                    break;
                }
                DecimalFormat df = new DecimalFormat();
                df.setParseIntegerOnly(true);
                double value = cell.getNumericCellValue();
                if (value % 1 == 0) {
                    cv = df.format(value);
                    cv = cv.replaceAll(",", "");
                } else {
                    if ((value + "").length() >= 7) {
                        value = Math.round(value * 10000);
                        value = value / 10000;
                        df = new DecimalFormat("0.0000");
                        cv = df.format(value);
                    } else {
                        cv = value + "";
                    }
                }
                break;
            case STRING:
                cv = cell.getRichStringCellValue().getString().trim();
                break;
            case BLANK:
                break;
            case FORMULA:
                try {
                    XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(cell.getRow().getSheet().getWorkbook());
                    cv = evaluator.evaluate(cell).getStringValue();
                } catch (Exception e) {

                }
                break;
            default:
                break;
        }
        return cv;
    }

    public static ExcelSheet match(String sheetName, List<ExcelSheet> excelSheets) {
        for (ExcelSheet sheet : excelSheets) {
            if (sheet.sheetName().equals(sheetName.trim())) {
                return sheet;
            }
        }
        return null;
    }

    private static <T> T transferBodyToEntity(int i, List<String> content, Class<T> classType, HashMap<String, Integer> headMap) throws Exception {
        T entry = classType.newInstance();
        ReflectionUtils.doWithFields(classType, new ReflectionUtils.FieldCallback() {
            @Override
            public void doWith(Field field) throws IllegalArgumentException, IllegalAccessException {
                ExcelColumn anno = field.getAnnotation(ExcelColumn.class);
                Translate translate = field.getAnnotation(Translate.class);
                if (null == anno) {
                    return;
                }
                field.setAccessible(true);
                Integer index = headMap.get(anno.value());
                if (null == index) {
                    return;
                }
                Object value = null;
                if (content.size() > index) {
                    value = content.get(index);
                }
                if (null != translate) {
                    Method m;
                    try {
                        m = translate.cls().getDeclaredMethod(translate.value(), String.class);
                        value = m.invoke(null, value);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                field.set(entry, value);
            }
        });
        return entry;
    }

    private static HashMap<String, Integer> parseHead(List<String> content, Class<?> classType) {
        HashMap<String, Integer> colNameIndexMap = Maps.newHashMap();
        Set<String> colNames = new HashSet<>();
        Field[] fs = classType.getDeclaredFields();
        for (Field f : fs) {
            f.setAccessible(true);
            ExcelColumn annotation = f.getAnnotation(ExcelColumn.class);
            if (null != annotation) {
                colNames.add(annotation.value());
            }
        }
        for (int i = 0; i < content.size(); i++) {
            String col = content.get(i);
            if (colNames.contains(col)) {
                colNameIndexMap.put(col, i);
            }
        }
        return colNameIndexMap;
    }

    @Data
    public static class Data1 {
        private String attr1;
    }

    @Data
    public static class Data2 {
        private Date attr2;
    }

    public static class CustomFunction {
        //日期格式化
        @CfssExcelFuncAnno("mjTest")
        public static String mjTest(String text) {
            if (text.equals("1")) {
                return "aaa";
            } else {
                return "bbb";
            }
        }
    }

    public static void main(String[] args) {
        String test = "#cfssAmt(${attr}, '###,###,###,##')";
        String test2 = "#cfssDate(${data2.attr2}, 'yyyy-MM-dd')";
        String test3 = "${data1.attr1} + '中文测试'";
        String test4 = "#mjTest(${attr})";
        String test5 = "#datas[0].attr1";
        String test6 = "#attr1";

        FunctionRegister.registerInternalFunction();
        FunctionRegister.registerCustomFunction(CustomFunction.class);

        HashMap<String, Object> data = Maps.newHashMap();

        ArrayList<Data1> data1s = Lists.newArrayList();
        Data1 data1 = new Data1();
        data1.setAttr1("2");
        Data2 data2 = new Data2();
        data2.setAttr2(new Date());

        data.put("attr", 12345678.1234);
        data.put("data1", data1);
        data.put("data2", data2);
        data.put("datas", data1s);

        data1s.add(data1);

        CfssPoiExporterContext cfssPoiExporterContext = new CfssPoiExporterContext(new SpelExpressionParser(), data);
        cfssPoiExporterContext.EVAL_CONTEXT.setVariables(data);

        String result1 = DefaultRowProcessor.resolve(test, cfssPoiExporterContext);
        String result2 = DefaultRowProcessor.resolve(test2, cfssPoiExporterContext);
        String result3 = DefaultRowProcessor.resolve(test3, cfssPoiExporterContext);
        String result4 = DefaultRowProcessor.resolve(test4, cfssPoiExporterContext);

        Expression expression2 = cfssPoiExporterContext.getSpelExpressionParser().parseExpression(test5);
//        cfssPoiExporterContext.EVAL_CONTEXT.setVariables(cfssPoiExporterContext.getRootObjectMap());

        String result6 = expression2.getValue(cfssPoiExporterContext.EVAL_CONTEXT, String.class);


//        StandardEvaluationContext context = new StandardEvaluationContext();
//        context.setVariables(data);
//        Method m = CfssExcelFunction.class.getDeclaredMethod("cfssDate", Date.class, String.class);
//        SpelExpressionParser spel = new SpelExpressionParser();
//        context.registerFunction("cfssDate", m);
//        String xx = spel.parseExpression("#cfssDate(#data2.attr2,'yyyy-MM-dd')+123").getValue(context, String.class);
//        String xy = spel.parseExpression("'123'+'456'").getValue(context, String.class);
//        System.out.println(xx);
//        System.out.println(xy);
//        cfssPoiExporterContext.getSpelExpressionParser().parseExpression(test2, new TemplateParserContext())
//                .getValue(cfssPoiExporterContext.EVAL_CONTEXT, String.class);

        System.out.println(result1);
        System.out.println(result2);
        System.out.println(result3);
        System.out.println(result4);
        System.out.println(result6);
    }
}
